package com.sanjishu.enterlab.electricity.sale.service;

import com.sanjishu.enterlab.electricity.sale.domain.CompanyInfo;
import com.sanjishu.enterlab.electricity.sale.domain.MonthElecData;
import com.sanjishu.enterlab.electricity.sale.domain.User;
import com.sanjishu.enterlab.electricity.sale.mapper.CompanyInfoMapper;
import com.sanjishu.enterlab.electricity.sale.mapper.MonthElecDataMapper;
import com.sanjishu.enterlab.electricity.sale.mapper.UserMapper;
import com.sanjishu.enterlab.electricity.sale.utils.ImportException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.math.BigDecimal;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class MonthELECDataService {

    @Autowired
    private MonthElecDataMapper monthElecDataMapper;
    @Autowired
    private CompanyInfoMapper companyInfoMapper;
    @Autowired
    private UserMapper userMapper;

    public List<MonthElecData> listByMonth(Integer year, Integer month) {
        return monthElecDataMapper.listAllByMonth(year, month);
    }

    public List<MonthElecData> checkImport(MultipartFile file) throws Exception{
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();
        List<MonthElecData> monthElecDataList = new ArrayList<>();
        Map<String, CompanyInfo> companyInfoMap = new HashMap<>();
        Map<String, User> userMap = new HashMap<>();
        for (int i = 1; i <= rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            MonthElecData monthElecData = checkRow(row, companyInfoMap, userMap);
            monthElecDataList.add(monthElecData);
        }
        return monthElecDataList;
    }

    public MonthElecData show(Integer id) {
        return monthElecDataMapper.show(id);
    }

    public void update(Integer id, Integer companyId, Integer userId, double saveElecNum,
                       double elecConsumption, double elecActual, double priceDifference,
                       double agencyFee, int year, int month) {
        MonthElecData monthElecData = monthElecDataMapper.selectByPrimaryKey(id);
        if (monthElecData != null) {
            monthElecData.setCompanyInfoId(companyId);
            monthElecData.setUserId(userId);
            monthElecData.setSaveElecNum(BigDecimal.valueOf(saveElecNum));
            monthElecData.setElecConsumption(BigDecimal.valueOf(elecConsumption));
            monthElecData.setElecActual(BigDecimal.valueOf(elecActual));
            monthElecData.setPriceDifference(BigDecimal.valueOf(priceDifference));
            monthElecData.setAgencyFee(BigDecimal.valueOf(agencyFee));
            monthElecData.setYear(year);
            monthElecData.setMonth(month);
            monthElecData.setUpdatedAt(new Date());
            monthElecDataMapper.updateByPrimaryKey(monthElecData);
        }
    }

    @Async
    @Transactional
    public void import2DB(List<MonthElecData> monthElecDataList) {
        for(MonthElecData monthElecData : monthElecDataList) {
            monthElecDataMapper.insert(monthElecData);
        }
    }

    private MonthElecData checkRow(Row row, Map<String, CompanyInfo> map, Map<String, User> userMap) throws ImportException {
        //公司名
        String companyName = checkCell(row, 0, "公司名");
        CompanyInfo companyInfo = checkCompanyInfo(row,"公司名", companyName, map);

        //销售
        String phone = checkCell(row, 1, "销售");
        User user = checkUser(row, "销售", phone, userMap);

        //用户节约电费
        String saveElecNum = checkCell(row, 2, "用户节约电费");
        //预估电量
        String elecConsumption = checkCell(row, 3, "预估电量");
        //实际电量
        String elecActual = checkCell(row, 4, "实际电量");
        //价差
        String priceDifference = checkCell(row, 5, "价差");
        //代理费
        String agencyFee = checkCell(row, 6, "代理费");
        //年
        String year = checkCell(row, 7, "年份");
        //月
        String month = checkCell(row, 8, "月份");
        MonthElecData monthElecData = new MonthElecData();
        monthElecData.setCompanyInfoId(companyInfo.getId());
        monthElecData.setUserId(user.getId());
        monthElecData.setSaveElecNum(BigDecimal.valueOf(Double.valueOf(saveElecNum)));
        monthElecData.setElecConsumption(BigDecimal.valueOf(Double.valueOf(elecConsumption)));
        monthElecData.setElecActual(BigDecimal.valueOf(Double.valueOf(elecActual)));
        monthElecData.setPriceDifference(BigDecimal.valueOf(Double.valueOf(priceDifference)));
        monthElecData.setAgencyFee(BigDecimal.valueOf(Double.valueOf(agencyFee)));
        monthElecData.setYear(Integer.valueOf(year));
        monthElecData.setMonth(Integer.valueOf(month));
        monthElecData.setCreatedAt(new Date());
        monthElecData.setUpdatedAt(new Date());
        return monthElecData;
    }

    private static final String ERROR_MSG_TEMPLATE = "第{0}行的 [{1}] 未填写!";
    private String checkCell(Row row, int index, String name) throws ImportException {
        Cell cell = row.getCell(index);
        int rowNum = row.getRowNum()+1;
        if (cell == null) {
            throw new ImportException(MessageFormat.format(ERROR_MSG_TEMPLATE,rowNum,name));
        }
        String str = cellText(cell);
        if (StringUtils.isBlank(str)) {
            throw new ImportException(MessageFormat.format(ERROR_MSG_TEMPLATE,rowNum,name));
        }
        return str;
    }


    private static final String ERROR_USER_TEMPLATE = "第{0}行的[{1}],[{2}]未能查询到!";
    private User checkUser(Row row, String name, String phone,
                                         Map<String, User> map) throws ImportException {
        User sale = map.get(phone);
        if (sale == null) {
            sale = userMapper.findByPhone(phone);
            if (sale == null) {
                int rowNum = row.getRowNum()+1;
                throw new ImportException(MessageFormat.format(ERROR_USER_TEMPLATE,rowNum,name,phone));
            } else {
                map.put(phone, sale);
            }
        }
        return sale;
    }

    private static final String ERROR_COMPANY_TEMPLATE = "第{0}行的[{1}],[{2}]未能查询到!";
    private CompanyInfo checkCompanyInfo(Row row, String name, String company,
                                         Map<String, CompanyInfo> map) throws ImportException {
        CompanyInfo companyInfo = map.get(company);
        if (companyInfo == null) {
            companyInfo = companyInfoMapper.findByName(company);
            if (companyInfo == null) {
                int rowNum = row.getRowNum()+1;
                throw new ImportException(MessageFormat.format(ERROR_COMPANY_TEMPLATE,rowNum,name,company));
            } else {
                map.put(company, companyInfo);
            }
        }
        return companyInfo;
    }

    private String cellText(Cell cell) {
        int type = cell.getCellType();
        switch (type) {
            case 1:
                return cell.getStringCellValue();
            case 0:
                double val = cell.getNumericCellValue();
                return String.valueOf(val);
            case 4:
                boolean b = cell.getBooleanCellValue();
                return String.valueOf(b);
        }
        return null;
    }
}
